import pandas as pd
import numpy as np

def main():
    month = '202411'
    # pre_year = '202312'
    # pre_month = '202410'
    pre_year_int = int(int(month[0:4]) - 1)
    pre_year = str(pre_year_int) + '12'
    if month[4:6] != '01':
        pre_month = str(int(int(month) - 1))
    else:
        pre_month = str(pre_year_int) + '12'
    month_int = int(month[4:6])
    # 税率
    tax_rate = 0.25
    # month_int = 11
    # Sheet3
    # 补充一个参数
    xlsx_name = 'D:/repos/sicost/' + month + '/吨钢利润,吨钢期间费用.xlsx'
    df_1 = pd.read_excel(xlsx_name)
    df_1 = df_1[['SERIAL_NUM', 'COMPANY_NAME', 'CURR_MONTH_VALUE_1', 'CURR_YEAR_VALUE_1', 'LAST_YEAR_VALUE_1', 'RATIO_VALUE_1']]
    df_1 = df_1.reset_index(drop=False)
    df_1.rename(columns={'index': 'INDEX'}, inplace=True)
    df_1_1 = df_1[df_1['COMPANY_NAME'] == '非钢铁企业合计']
    df_1_1 = df_1_1.reset_index(drop=True)

    success = df_1_1.empty is False
    if success is False:
        print('空的不需要处理')
    else:
        index_tmp = df_1_1.loc[0]['INDEX']
        df_1_2 = df_1[df_1['INDEX'] < index_tmp]
        df_1_2 = df_1_2.reset_index(drop=True)
        df_1 = df_1_2
    df_1.drop(['INDEX'], axis=1, inplace=True)
    df_1['SERIAL_NUM_STR'] = df_1['SERIAL_NUM'].astype(str)
    def __cal_strincludenum(x):
        if '0' in x.SERIAL_NUM_STR:
            rst = 1
        elif '1' in x.SERIAL_NUM_STR:
            rst = 1
        elif '2' in x.SERIAL_NUM_STR:
            rst = 1
        elif '3' in x.SERIAL_NUM_STR:
            rst = 1
        elif '4' in x.SERIAL_NUM_STR:
            rst = 1
        elif '5' in x.SERIAL_NUM_STR:
            rst = 1
        elif '6' in x.SERIAL_NUM_STR:
            rst = 1
        elif '7' in x.SERIAL_NUM_STR:
            rst = 1
        elif '8' in x.SERIAL_NUM_STR:
            rst = 1
        elif '9' in x.SERIAL_NUM_STR:
            rst = 1
        else:
            rst = 0
        return rst


    df_1['include_type'] = df_1.apply(lambda x: __cal_strincludenum(x), axis=1)
    df_1 = df_1[(df_1['include_type'] == 1) | (df_1['COMPANY_NAME'] == '钢铁企业合计')]
    df_1 = df_1.reset_index(drop=True)

    df_1.drop(['SERIAL_NUM_STR'], axis=1, inplace=True)
    df_1.drop(['include_type'], axis=1, inplace=True)
    df_1.rename(columns={'CURR_MONTH_VALUE_1': '吨钢利润_本月数'}, inplace=True)
    df_1.rename(columns={'CURR_YEAR_VALUE_1': '吨钢利润_本年累计'}, inplace=True)
    df_1.rename(columns={'LAST_YEAR_VALUE_1': '吨钢利润_去年同期'}, inplace=True)
    df_1.rename(columns={'RATIO_VALUE_1': '吨钢利润_增减量'}, inplace=True)


    xlsx_name = 'D:/repos/sicost/' + month + '/实现利税,利润总额.xlsx'
    df_2 = pd.read_excel(xlsx_name)
    df_2 = df_2[['COMPANY_NAME', 'CURR_MONTH_VALUE_2', 'CURR_YEAR_VALUE_2']]
    df_2.rename(columns={'CURR_MONTH_VALUE_2': '利润总额_本月数'}, inplace=True)
    df_2.rename(columns={'CURR_YEAR_VALUE_2': '利润总额_本年累计'}, inplace=True)

    xlsx_name = 'D:/repos/sicost/' + month + '/销售利润率,净资产收益率.xlsx'
    df_3 = pd.read_excel(xlsx_name)
    df_3 = df_3[['COMPANY_NAME', 'CURR_MONTH_VALUE_1', 'CURR_YEAR_VALUE_1']]
    df_3.rename(columns={'CURR_MONTH_VALUE_1': '销售利润率_本月数'}, inplace=True)
    df_3.rename(columns={'CURR_YEAR_VALUE_1': '销售利润率_本年累计'}, inplace=True)
    v = ['COMPANY_NAME']
    df_0 = pd.merge(df_1, df_2, on=v, how='left')
    df_0 = pd.merge(df_0, df_3, on=v, how='left')
    df_0 = df_0[df_0['COMPANY_NAME'] != '钢铁企业合计']
    df_0 = df_0.reset_index(drop=True)

    writer = pd.ExcelWriter('D:/repos/sicost/' + month + '/NEW_吨钢利润.xlsx')
    df_0.to_excel(writer, sheet_name='Sheet1', index=False)
    writer.save()
    xlsx_name = 'D:/repos/sicost/海外/海外数据.xlsx'
    df_5 = pd.read_excel(xlsx_name)
    df_5 = df_5[df_5['IND_NAME'] == '吨钢利润']
    df_5 = df_5[['COMPANY_NAME', 'DATE_VER', 'IND_VALUE']]
    df_5.rename(columns={'IND_VALUE': '吨钢利润'}, inplace=True)
    def __cal_date_ver_new(x):
        rst1 = x.DATE_VER[0:4]
        if x.DATE_VER[4:6] == 'Q1':
            rst2 = '11'
        elif x.DATE_VER[4:6] == 'H1':
            rst2 = '22'
        elif x.DATE_VER[4:6] == 'Q3':
            rst2 = '33'
        elif x.DATE_VER[4:6] == 'H2':
            rst2 = '44'
        else:
            rst2 = '55'
        rst = rst1 + rst2
        return rst


    df_5['DATE_VER_NEW'] = df_5.apply(lambda x: __cal_date_ver_new(x), axis=1)
    latest_data_ver = df_5.iloc[-1]['DATE_VER_NEW']
    df_5_1 = df_5[df_5['DATE_VER_NEW'] == latest_data_ver]
    df_5_1.drop(['DATE_VER_NEW'], axis=1, inplace=True)
    df_5_1.drop(['DATE_VER'], axis=1, inplace=True)
    df_5_1 = df_5_1.reset_index(drop=True)
    dict_0 = {}
    for index, row in df_5_1.iterrows():
        dict_0['COMPANY_NAME'] = row['COMPANY_NAME']
        dict_0['吨钢利润_本年累计'] = row['吨钢利润']
        new_row = pd.Series(dict_0)
        df_0 = df_0.append(new_row, ignore_index=True)

    # df_out = pd.DataFrame(columns=['百分位', '分位值'])
    df_out = pd.DataFrame(columns=['PR', 'PR_VALUE'])
    dict_out = {}
    for i in range(1, 19):
        # print((i+1)*5)
        percentiles = (i+1) * 5 / 100
        dict_out['PR'] = str(int((i+1) * 5)) + '分位'
        quantiles = df_0['吨钢利润_本年累计'].quantile(percentiles)
        dict_out['PR_VALUE'] = quantiles
        new_row = pd.Series(dict_out)
        df_out = df_out.append(new_row, ignore_index=True)
    writer = pd.ExcelWriter('D:/repos/sicost/' + month + '/NEW_吨钢利润_PR.xlsx')
    df_out.to_excel(writer, sheet_name='Sheet1', index=False)
    writer.save()
    return 1

print('finish')
